#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2021/10/19
# @Author  : ZhangAiqiang
# @FileName: database.py
import pymysql
from pymysql.constants import CLIENT
from datetime import datetime
HOST='10.3.180.40'
PORT = 3306
USER = 'test' 
PASSWORD = 'liushh;123DCS'
DATABASE = 'archive' 
TABLE = 'KunShan_LVP'
class HVData:
    def __init__(self,configs=None, host=HOST, port=PORT, user=USER, password=PASSWORD, database=DATABASE):
        self.connection = pymysql.connect(host=host, port=port,
                             user=user,
                             password=password,
                             database=database,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             client_flag=CLIENT.MULTI_STATEMENTS)
        self.host = host
        self.user = user
        if configs!=None:
            self.voltUL = configs['VUL']
            self.voltLL = configs['VLL']
            self.currentUL = configs['CUL']
            self.currentLL = configs['CLL']
            self.tUL = configs['TUL']
            self.tLL = configs['TLL']
        # self.table = table
    def __enter__(self):
        self.connection.begin()
        print('Database Host={} begin transanction'.format(self.host))
        return self
    def __exit__(self, exc_type, exc_value, exc_traceback):
        print('[__exit__ {}]: '.format('DataBase'))
        # exc_traceback为None，代表没有异常
        if exc_traceback is None:
            self.connection.close()
            print('正常退出')
        else:
            print('自动关闭socket')
            self.connection.close()
    def execSQLcommit(self, sql):
        with self.connection.cursor() as cursor:
            # Create a new record
            # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql)
        self.connection.commit()
    def execSQLread(self, sql):
        with self.connection.cursor() as cursor:
            # Create a new record
            # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql)
            result = cursor.fetchone()
            return result
    def execSQLreadAll(self, sql):
        with self.connection.cursor() as cursor:
            # Create a new record
            # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql)
            result = cursor.fetchall()
            return result
    def storeHV(self, ip, temper, fans_rpm, present_time, accumulated_time, id=-1):
        fans_rpm = [int(i) for i in fans_rpm]
        if id<0:
            self.execSQLcommit('insert into {} (ip, temper, fans_rpm0, fans_rpm1, fans_rpm2, fans_rpm3,present_time, accumulated_time) values ("{}", {},{},{},{},{},{},{});'.format('KunShan_LVS',ip, temper, fans_rpm[0],fans_rpm[1],fans_rpm[2],fans_rpm[3], present_time, accumulated_time))
            return 
        else:
            self.execSQLcommit('update {} set (ip, temper, fans_rpm0, fans_rpm1, fans_rpm2, fans_rpm3,present_time, accumulated_time) values ("{}", {},{},{},{},{},{},{}) where id={} ;'.format(
                'KunShan_LVS',ip, temper, fans_rpm[0],fans_rpm[1],fans_rpm[2],fans_rpm[3], present_time, accumulated_time,id))
    def storeCh(self, number, ch,chinfo):
        self.execSQLcommit('insert into {} (STAT0_CH_ID, STAT1_GREEN_LED, STAT2_RED_LED, STAT3_S_RUNNING,STAT4_S_ERR,STAT5_GREEN_LED,STAT6_RED_LED,STAT7_M_RUNNING,STAT8_M_ERR,STAT9_OUTPUT,STAT10_FAULT_CODE,date_time) values ({},{},{},{},{},{},{},{},{},{},{},"{}");'.format(
            'KunShan_LVS'+number,ch,chinfo[9],chinfo[8],chinfo[7],chinfo[6],chinfo[5],chinfo[4],chinfo[3],chinfo[2],chinfo[1],chinfo[0],datetime.now()))
    def storeInfo(self,number,temper, fans_rpm, present_time, accumulated_time,chinfo):
        keys = ['VCH_', 'CCH_']
        sql='insert into KunShan_LVP{} (temper, fans_rpm0, fans_rpm1, fans_rpm2, fans_rpm3,present_time, accumulated_time'.format(number)
        for key in keys:
            sql += ','+','.join(['{}{:0>2d}'.format(key,i) for i in range(1,26)])
        sql += ') values ('+','.join(['{}']*(7+50))+');'
        volts = [chi[10] for chi in chinfo]
        current = [chi[11] for chi in chinfo]
        self.execSQLcommit(sql.format(temper, fans_rpm[0], fans_rpm[1], fans_rpm[2], fans_rpm[3],present_time, accumulated_time,*volts,*current))
        alarms = []
        if temper>self.tUL:
            self.alarm('KunShan_LVP'+number,'temper',temper,'higher than temperature limit','LVP')
            alarms.append([number, 0, 'temper',temper])
        if temper<self.tLL:
            self.alarm('KunShan_LVP'+number,'temper',temper,'lower than temperature limit','LVP')

        for i,chi in enumerate(chinfo):
            if i<24:
                if chi[10]>self.voltUL:
                    self.alarm('KunShan_LVP'+number,'VCH{:0>2d}'.format(i+1),chi[10],'higher than voltage limit','LVP')
                    alarms.append([number, i+1, 'voltage', chi[10]])
                if chi[10]<self.voltLL and chi[10]>13.5:
                    self.alarm('KunShan_LVP'+number,'VCH{:0>2d}'.format(i+1),chi[10],'lower than voltage limit','LVP')
                if chi[11]>self.currentUL:
                    self.alarm('KunShan_LVP'+number,'CCH{:0>2d}'.format(i+1),chi[11],'higher than current limit','LVP')
                    alarms.append([number, i+1, 'current', chi[11]])
                if chi[11]<self.currentLL:
                    self.alarm('KunShan_LVP'+number,'CCH{:0>2d}'.format(i+1),chi[11],'lower than current limit','LVP')

            self.storeCh(number,i+1,chi)
        return alarms
    def alarm(self,tablename,parameter,value,description,softwareid):
        sql = 'insert into KunShan_alarm (TableName,Parameter,Value,Description,SoftwareID) values ("{}","{}",{},"{}","{}")'.format(tablename,parameter,value,description,softwareid)
        self.execSQLcommit(sql)
    def initHVTable(self):
        sql = "CREATE TABLE `KunShan_LVS` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,\
            `IP` char(15) NOT NULL,\
            `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,\
            `temper` int(10) unsigned DEFAULT NULL,\
            `fans_rpm0` smallint(5) unsigned DEFAULT NULL,\
            `fans_rpm1` smallint(5) unsigned DEFAULT NULL,\
            `fans_rpm2` smallint(5) unsigned DEFAULT NULL,\
            `fans_rpm3` smallint(5) unsigned DEFAULT NULL,\
            `present_time` int(10) unsigned DEFAULT NULL,\
            `accumulated_time` int(10) unsigned DEFAULT NULL,\
            PRIMARY KEY (`id`) USING BTREE,\
            KEY `date_time` (`date_time`) USING BTREE) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED"
        self.execSQLcommit(sql)
    def initChTable(self):
        # sql = "alter table KunShan_LVP; add CONSTRAINT fk_HVCh; foreign key (HV_Id); references KunShan_LVS(id);"
        sql = "alter table KunShan_LVP add HV_Id int(10) unsigned;"
        self.execSQLcommit(sql)
    def getIpByNumber(self, number):
        result = self.execSQLread('select LVS_IP from KunShanLVS_IP where Number={}'.format(number))
        return result['LVS_IP']
    def setIpByNumber(self, number, ip):
        result = self.execSQLcommit('update KunShanLVS_IP set LVS_IP = "{}" where Number="{}";'.format(ip, number))
        return result
if __name__=='__main__':
    hvdata=HVData()
    # hvdata.execSQLcommit()
    hvdata.execSQLreadAll('show DATABASES;')
    hvdata.execSQLreadAll('show TABLES;')
    # hvdata.execSQLreadAll('alter table KunShan_LVP;add  ;')